#!/usr/bin/perl
  
use strict;  
use warnings;
use Spreadsheet::ParseExcel;  
use Path::Class;
use autodie; # die if problem reading or writing a file
use XML::DOM;
use XML::Twig;

our $parser = undef;  
our $workbook = undef;

our @MOList = ();
our $measurementType = undef;
our @counters = ();

our $year = '2013';
our $month = '3';
our $day = '20';
our $sqlID = '';


sub GetIndexByColName{
	
	my ($mesSheet, $colName, $rowMin, $rowMax, $colMin, $colMax) = @_;
	
	for my $row ( $rowMin .. $rowMax) {
		
		for my $col ( $colMin .. $colMax) {
		
			my $cell = ( $mesSheet->get_cell( $row, $col));
			next unless $cell;
			my $str = $cell->value();
			
			$str =~ s/^\s+//;  # romove leading whitespace
			$str =~ s/\s+$//;  # romove trailing whitespace
			
			if($str eq $colName){
				return ($row,$col);
			}
		}
	}
	
	exit;
}

sub GetRangeCounter{
	my $risePath = "./Input/Rise/"; 
	my @fileNames; 

	opendir (DIR, $risePath) or die "can't open it:$!"; 
	@fileNames = readdir DIR;
	closedir(DIR);
	
	my $counterRangeFile = "./Output/counterRange.txt";
	open(InFile,">$counterRangeFile") || die("Can not open the file!$!n");

	foreach my $file (@fileNames) {

		next if ($file eq '.' or $file eq '..');
		$file = $risePath.$file;
		print "******file: $file\n";
		my $riseWorkbook = $parser->Parse($file);

		my $measurementSheet = $riseWorkbook->worksheet("Counters");
		my ( $rowMin, $rowMax ) = $measurementSheet->row_range();
		my ( $colMin, $colMax ) = $measurementSheet->col_range();
		
		my ($counterRow,$counterCol) = GetIndexByColName($measurementSheet, "PI ID", $rowMin, $rowMax, $colMin, $colMax);
		my ($rangeRow,$rangeCol) = GetIndexByColName($measurementSheet, "Range", $rowMin, $rowMax, $colMin, $colMax);
		
		for my $row ( $rangeRow+2 .. $rowMax) {
		
			my $counterCell = $measurementSheet->get_cell( $row, $counterCol);
			my $rangeCell = $measurementSheet->get_cell( $row, $rangeCol);
			next unless $counterCell;
			next unless $rangeCell;
			
			my $conterName = $counterCell->value();
			my $rangeValue = $rangeCell->value();
			
			next if ($conterName eq '' or $rangeValue eq '');
			next if ($rangeValue eq '16 bit' or $rangeValue eq '32 bit' or $rangeValue eq '32 bits' 
						or $rangeValue eq '32bit' or $rangeValue eq '32 bit value'  
						or $rangeValue eq '31 bit value' or $rangeValue eq 'N/A' 
						or $rangeValue eq '>=0' or $rangeValue eq '>=0.0' 
						or $rangeValue eq '0...10000' or $rangeValue eq '0-2^32');
			print InFile $conterName.": ".$rangeValue."\n";
		}
	}
	
	close(InFile);
}

sub GetRandomValueBaseOnRange{

	my $range = shift;
	
	my $randomValue;
	my $pos1 = index($range, "..");
	my $pos2 = index($range, "...");
	my $pos3 = index($range, "-");
	
	my @values;
	if($pos1 != -1){
		@values = split(/\.+/, $range);
	}elsif($pos2 != -1){
		@values = split(/\.+/, $range);
	}elsif($pos3 != -1){
		@values = split(/-/, $range);
	}else{
		return 0;
	}
	
	my $min = shift(@values);
	$min =~ s/^\s+//;  # romove leading whitespace
	$min =~ s/\s+$//;  # romove trailing whitespace

	my $maxStr = shift(@values);
	$maxStr =~ s/^\s+//;  # romove leading whitespace
	$maxStr =~ s/\s+$//;  # romove trailing whitespace
	
	my @arr = split(/\s+/, $maxStr);
	my $max = shift(@arr);
	my $maxFlag = index($max, "%");
	
	if($maxFlag != -1){
	
		chop($min);
		chop($max);
		$randomValue=rand($max-$min)+$min;
		$randomValue=sprintf("%.2f", $randomValue);
		# $randomValue .= "%";			
	}elsif($max == 1){
		$randomValue=rand($max-$min)+$min;
		$randomValue=sprintf("%.2f", $randomValue);
	}else{
		$randomValue=int(rand($max-$min))+$min;
	}
	
	print "---min:$min---max:$max---randomValue:$randomValue---\n";
	
	return $randomValue;
}

sub UpdateOMesFiles{

	my $counterRangeFile = "./Output/counterRange.txt";
	open(OutFile,"<$counterRangeFile") || die("Can not open the file!$!n");
	my @lines = <OutFile>;
	close(OutFile);
	
	foreach my $line(@lines){
	
		chomp($line);
		print "---line: $line\n";
		my @list = split(/:/, $line);
		my $counter = shift(@list);
		my $range = pop(@list);		
		my $pos = index($counter, "C");
		my $rangeMeaNum = substr($counter, 0, $pos);
		
		my $randomValue = GetRandomValueBaseOnRange($range);
		
		my @fileNames; 
		my $OMesFilesPath = "./Output/OMeSFiles_";
		$OMesFilesPath .= $year.$month.$day;
		opendir (DIR, $OMesFilesPath) or die "can't open it:$!"; 
		@fileNames = readdir DIR;
		closedir(DIR);
		
		my $tmpFile = $OMesFilesPath."/tmp.xml";	
		foreach my $file (@fileNames) {
			my $pos = index($file, ".xml");
			if ($pos != -1){
				my $str = substr($file, 0, $pos);
				my @list = split(/-/, $str);
				pop(@list);
				
				my $subStr = pop(@list);
				my @subList = split(/_/, $subStr);
				my $meaNum = shift(@subList);
				
				if($meaNum eq $rangeMeaNum){
					my $omesFile = $OMesFilesPath."/".$file;
					print "---omesFile: $omesFile\n";
					open(OMES_FILE,"<$omesFile") || die("Can not open the file!$!n");
					open(TMP_FILE,">$tmpFile") || die("Can not open the file!$!n");
					
					while(<OMES_FILE>){
					
						if($_ =~ /<$counter>/){
							
							$_ =~ s/<$counter>.*<\/$counter>/<$counter>$randomValue<\/$counter>/;
							print "******line: $_\n";
						}
						
						print TMP_FILE $_;
						
					}
					
					close(OMES_FILE);
					close(TMP_FILE);
					rename($tmpFile, $omesFile);
				}
			}
		}
	}
}

sub CorrectCounterValueByRise{

	GetRangeCounter;
	UpdateOMesFiles;
}

sub GenOMesSQLInquireCmdFile{
	
	my @fileNames; 
	my @meaInfo;
	
	my $OMesFilesOutputPath = "./Output/OMeSFiles_"; 
	$OMesFilesOutputPath .= $year.$month.$day;
	opendir (DIR, $OMesFilesOutputPath) or die "can't open it:$!"; 
	
	@fileNames = readdir DIR;
	closedir(DIR);
	foreach my $file (@fileNames) {
		# print "******file: $file\n";
		my $pos = index($file, ".xml");
		if ($pos != -1){
			my $str = substr($file, 0, $pos);
			my @list = split(/-/, $str);
			pop(@list);
			my $head = pop(@list);
			
			push(@meaInfo, $head);
		}
	}

	my $tableFile = "./Input/";
	$tableFile .= "nokrww_table.txt";
	open(OutFile,"<$tableFile") || die("Can not open the file!$!n");
	my @line = <OutFile>;
	close(OutFile);
	
	my @sqlCmd;
	if($sqlID and $sqlID !~ /WHERE/){
		$sqlID = " WHERE ".$sqlID;
	}
	
	# @meaInfo = ("M1021_IUPC", "M1018_RCPMUEQ", "M5107_FATMVP");
	foreach my $meaMid (@meaInfo){
		# print "******meaMid: $meaMid\n";
		my @list = split(/_/, $meaMid);
		my $mid = pop(@list);
		
		foreach my $table(@line){
			# print "******table: $table\n";
			
			my $pos = index( $table, "_".$mid."_");
			if($pos != -1){
				$table =~ s/^\s+//;  # romove leading whitespace
				$table =~ s/\s+$//;  # romove trailing whitespace			

				my $cmd = "SELECT * FROM NOKRWWRAW.".$table.$sqlID." ORDER BY PERIOD_START_TIME DESC;";
				$cmd = sprintf("%-120s", $cmd);
				my $tail = " --- ".$meaMid;
				push(@sqlCmd, $cmd.$tail);
			}
		}
	}
	
	my $sqlCmdFile = "./Output/sqlCmd.txt";
	open(InFile,">$sqlCmdFile") || die("Can not open the file!$!n");
	foreach my $line ( @sqlCmd ) {
		# Add the line to the file
		print InFile $line."\n";
	}
	close(InFile);
	
	print "******Generate sql command file: sqlCmd.txt\n";
}

sub GenOMesFilesByMeasurement{
	
	my ($meaNum, $objectLevel, $meaTypeElemName, $mid) = @_;

	my $OMesFilesOutputPath = "./Output/OMeSFiles_"; 	
	$OMesFilesOutputPath .= $year.$month.$day;
	my $dir = dir($OMesFilesOutputPath); # /tmp
	unless( -e $dir ){
		mkdir ($dir);
	}
	
	my $fileHead = "omes_fqdn_PLMN-PLMN%2FOMS-313_NE-OMS-313-";
	my $fileName = $fileHead.$objectLevel."-".$meaNum."_".$mid."-tmpl.xml";
	my $file = $dir->file($fileName); # /tmp/file.txt

	my $fileHandle = $file->openw();
	my $xmlDoc = XML::DOM::Document->new();
	
	# declaration part "<?xml version="1.0" ?>"	
	my $xmldecl = $xmlDoc->createXMLDecl('1.0');
	$xmlDoc->setXMLDecl($xmldecl);

	# <OMeS xmlns="pm/cnf_rnc_nsn.5.0.xsd">
	my $omesElem = $xmlDoc->createElement('OMeS');
	$omesElem->setAttribute('xmlns', "pm/cnf_rnc_nsn.5.0.xsd");
	$xmlDoc->appendChild($omesElem);

	# <PMSetup interval="60" startTime="2013-03-02T17:03:54.000+02:00:00">
	my $interval = "60";
	my $hour=int(rand(24));
    my $minute=int(rand(60));
    my $second=int(rand(60));
    ($hour, $minute, $second) = (sprintf("%02d", $hour), sprintf("%02d", $minute), sprintf("%02d", $second));
	
	my $startTime = "$year-$month-$day"."T"."$hour:$minute:$second.000+02:00:00";
	my $PMSetupElem = $xmlDoc->createElement('PMSetup');
	$PMSetupElem->setAttribute('interval', $interval);
	$PMSetupElem->setAttribute('startTime', $startTime);
	$omesElem->appendChild($PMSetupElem);	

	# <PMMOResult>
	my $PMMOResultElem = $xmlDoc->createElement('PMMOResult');
	$PMSetupElem->appendChild($PMMOResultElem);

	# my @MOList = (1,2);
	foreach my $mo (@MOList){
		
		# <MO>
		my $moElem = $xmlDoc->createElement('MO');
		$PMMOResultElem->appendChild($moElem);
		
		my @moList = split(/&/, $mo);
		my $baseId = '';
		my $localMOId = '';		
		
		$baseId = shift(@moList);
		$localMOId = shift(@moList);
		
		# <baseId>
		if($baseId){
			my $basicidElem = $xmlDoc->createElement('baseId');
			$basicidElem->appendChild($xmlDoc->createTextNode($baseId));
			$moElem->appendChild($basicidElem);
		}
		
		# localMoid
		if($localMOId){
		
			my $localmoidElem;
			my $dn;
			if($baseId){
				$dn = "DN:".$localMOId;
				$localmoidElem = $xmlDoc->createElement('localMoid');
			}else{
				$dn = "PLMN-PLMN/".$localMOId;
				$localmoidElem = $xmlDoc->createElement('DN');
			}
			
			$localmoidElem->appendChild($xmlDoc->createTextNode($dn));
			$moElem->appendChild($localmoidElem);
			
			# MOName
			if( $objectLevel eq "RNC" ){
			
				my $pos1 = index( $localMOId, 'NE-');
				my $pos2 = index( $localMOId, 'MNC');
				if($pos1 == -1 &&  $pos2 == -1){
					
					my $moName = "NE-RNC-313_DN:".$localMOId;
					my $moNameElem = $xmlDoc->createElement('MOName');
					$moNameElem->appendChild($xmlDoc->createTextNode($moName));
					$moElem->appendChild($moNameElem);	
				}
			}
		}
	}
		
	# object element
	# my $object = 'WBTS_Nokia_5.0';
	my $object = $meaTypeElemName."_Nokia_5.0";
	my $meaType = $measurementType;
	my $objectElem = $xmlDoc->createElement($object);
	$objectElem->setAttribute('measurementType', $meaType);
	$PMMOResultElem->appendChild($objectElem);

	foreach my $line ( @counters ) {
		# Add the line to the file

		next unless $line;
		
		# element for counter value
		
		# my $counterValue = "2013";
		my $counterValue = int(rand(6000));
		my $counterElem = $xmlDoc->createElement($line);

		$counterElem->appendChild($xmlDoc->createTextNode($counterValue));

		$objectElem->appendChild($counterElem);

	}

	# format the xml string
	my $twig = new XML::Twig;
	$twig->set_indent(" "x4);
	$twig->parse($xmlDoc->toString);
	$twig->set_pretty_print("indented");

	# save the xml to a file
	$fileHandle->print($twig->sprint);
}

sub GetMeasurementMID{

	my ($mesSheet, $row, $col) = @_;
	
	my $midCell = $mesSheet->get_cell( $row, $col);
	my $str = $midCell->value();
	$str =~ s/^\s+//;  # romove leading whitespace
	$str =~ s/\s+$//;  # romove trailing whitespace
	
	return $str;
}

sub GetMeasurementObjectLevel{

	my ($mesSheet, $row, $col) = @_;
	
	my $objectLevel = '';
	my $meaTypeElemName = '';
	
	# e.g. RNC-WBTS-WCEL, MCC-MNC / RNC, MCC-MNC / RNC-ROUTE
	my $objectLevelCell = $mesSheet->get_cell( $row, $col);
	my $str = $objectLevelCell->value();
	if($str eq ''){
		return;
	}else{
		my $pos = index($str, ',');
		if ($pos == -1){
			$str =~ s/^\s+//;  # romove leading whitespace
		    $str =~ s/\s+$//;  # romove trailing whitespace
			# $objectLevel = $str;
		}else{
			$str = substr($str, 0, $pos);
		}
		
		if( index($str, 'FTM') != -1 ){
			$objectLevel = "FTM";
			$meaTypeElemName = "WBTS";
		}elsif( index($str, 'WBTS') != -1 ){
			$objectLevel = "WBTS";
			my @object = split(/-/, $str);
			$meaTypeElemName = pop(@object);
		}else{
			$objectLevel = "RNC";
			$meaTypeElemName = "RNC";
		}
	}
	
	return ($objectLevel, $meaTypeElemName);
}

sub GetMeasurementCounter{

	my $sheetName = shift;
	my $meaNum = shift;
	print "The arg : $sheetName.\n\n";
	next unless $sheetName;
	
	my $counterSheet = $workbook->worksheet($sheetName);
	my ( $rowMin, $rowMax ) = $counterSheet->row_range(); 
	
	my $emptyRowNum = 0;
	for my $row ( $rowMin .. $rowMax) {
		my $cell = $counterSheet->get_cell( $row, 0);
		# next unless $cell;
		
		unless ($cell){
			$emptyRowNum++;
			if($emptyRowNum > 3){
				last;
			}
			
			next;
		}
		
		# get the font color for each cell, if the font color : red, remove this line
		my $colorIndex = $cell->{Format}->{Font}->{Color};
		next if( $colorIndex == 10);
		
		my $counterStr = $cell->value();
		if($counterStr eq ''){
			$emptyRowNum++;
			if($emptyRowNum > 3){
				last;
			}
			
			next;
		}
		
		$emptyRowNum = 0;
		
		my $pos1 = index($counterStr, $meaNum."C");
		my $pos2 = index($counterStr, $meaNum."B");
		next if ($pos1 == -1 && $pos2 == -1);
		$counterStr =~ s/^\s+//;  # romove leading whitespace
		$counterStr =~ s/\s+$//;  # romove trailing whitespace
		push(@counters, $counterStr);
		#print "This counter name : $counterStr.\n";
	}
	
}

sub GetMeasurementDimension{
	
	my ($mesSheet, $row, $col) = @_;
	
	# demension 1-6
	foreach my $index (1..6){
		
		my $demension = $mesSheet->get_cell( $row, $col+$index-1);
		next unless $demension;
		my $str = $demension->value();
		if ($str eq ''){
		
			last;
		}else{
			
			$str =~ s/^\s+//;  # romove leading whitespace
			$str =~ s/\s+$//;  # romove trailing whitespace
			
			my $pos1 = index($str, 'PLMN-RNC');
			my $pos2 = index($str, 'PLMN');
			
			my $baseId = '';
			my $localMoid = '';
			my $mo = '';
			
			if($pos1 != -1){
				$baseId = 'NE-RNC-313';
				$str = substr($str, 8);
			}elsif($pos2 != -1){
				$str = substr($str, 4);
			}else{
				next;
			}			
			
			my @mngrObjects = split(/-/, $str);
			
			while(@mngrObjects){
				my $object = shift(@mngrObjects);
				next unless $object;
				
				if( $object eq "WBTS" ){
					$localMoid .= "NE-WBTS-159";
				}else{
					$localMoid .= $object."-1";
				}
				
				if(@mngrObjects){
					$localMoid .= "/";
				}
			}
			
			$mo = $baseId."&".$localMoid;
			
			if($mo ne "&"){
				push(@MOList, $mo);
			}
			
			print "The demension : $mo\n";
		}
	}
	
	print "The DN of this measurement : @MOList\n";
}

sub GenOMesFilesByAD{
	
	my $measurementSheet = $workbook->worksheet("Measurements");
	my ( $rowMin, $rowMax ) = $measurementSheet->row_range();
	my ( $colMin, $colMax ) = $measurementSheet->col_range();
	
	my ($omesNameRow,$omesNameCol) = GetIndexByColName($measurementSheet, "OMeS Name", $rowMin, $rowMax, $colMin, $colMax);
	my ($dimension1Row,$dimension1Col) = GetIndexByColName($measurementSheet, "Dimension 1", $rowMin, $rowMax, $colMin, $colMax);
	# my ($measurementNumRow,$measurementNumCol) = GetIndexByColName($measurementSheet, "Measurement Name", $rowMin, $rowMax, $colMin, $colMax);
	my ($meaWorksheetRow,$meaWorksheetCol) = GetIndexByColName($measurementSheet, "Measurement worksheet", $rowMin, $rowMax, $colMin, $colMax);
	my ($objectLevelRow,$objectLevelCol) = GetIndexByColName($measurementSheet, "Level IDs in KOALA", $rowMin, $rowMax, $colMin, $colMax);
	my ($midRow,$midCol) = GetIndexByColName($measurementSheet, "MID", $rowMin, $rowMax, $colMin, $colMax);
	
	my $emptyRowNum = 0;
	$rowMin = $omesNameRow + 2;
	my $row = $rowMin;
	for ( $rowMin .. $rowMax) {
		my $omesNamecell = $measurementSheet->get_cell( $row, $omesNameCol);
		if($omesNamecell->value() eq ''){
			$row++;
			$emptyRowNum++;
			
			if($emptyRowNum > 2){
				last;
			}
			
			next;	
		}
		$emptyRowNum = 0;
		print "The Row, col = ( $row, $omesNameCol)\n";	
		# get the font color and background color for each cell
		my $colorIndex = $omesNamecell->{Format}->{Font}->{Color};
		my $colorRgb = $parser->ColorIdxToRGB($colorIndex);
		my $bgColorIndex = $omesNamecell->{Format}->{Fill}->[1];
		my $bgColorRgb = $parser->ColorIdxToRGB($bgColorIndex);
		print "The Font index color : $colorIndex. \n";
		# print "Font rgb color : $colorRgb. \n";
		# print "Background color : $bgColorRgb\n";		
		
		# if the font color : red, remove this line
		# next if( $colorIndex == 10);
		if( $colorIndex == 10){
			print "************************************************************\n";
			$row++;
			next;
		}
		
		# get the MO info
		GetMeasurementDimension($measurementSheet, $row, $dimension1Col);
		
		# get measurement type
		$measurementType = $omesNamecell->value();
		$measurementType =~ s/^\s+//;  # romove leading whitespace
		$measurementType =~ s/\s+$//;  # romove trailing whitespace
		print "The measurement type : $measurementType\n";	

		#####################################################################################
		# # get the measurement number		
		# my $measureNumCell = $measurementSheet->get_cell( $row, $measurementNumCol);
		# my $str = $measureNumCell->value();
		# my $pos1 = index($str, "(M");
		# my $pos2 = index($str, ")");
		# next if $pos1 == -1 || $pos2 == -1;
		# my $measurementNum = substr($str, $pos1+1, $pos2-$pos1-1);
		# print "The final str : $measurementNum.\n";
		#
		# #judge whether this cell : merge
		# my $mergeState = $measureNumCell->is_merged();
		# $mergeState = $mergeState == 1? 1:0;
		# print "This cell merge state : $mergeState.\n";
		#####################################################################################
		
		
		
		# get counter sheet name
		my $specCounterFlag = 0;
		my $meaNum = 0;
		my $flag = 1;
		my $num = 0;
		while($flag){
		
			my $counterSheetCell = $measurementSheet->get_cell( $row+$num, $meaWorksheetCol );
			my $counterSheetName = $counterSheetCell->value();

			last unless $counterSheetName;
			
			# get the measurement number
			my $pos1 = index($counterSheetName, ",");
			# next if $pos1 == -1;
			if ($pos1 == -1){
				print "\n";
				last;
			}
			
			my $meaNumStr = substr($counterSheetName, 0, $pos1);
			
			# e.g. M1001_1, M1001_2, M1002
			my $pos2 = index($meaNumStr, "_");
			if ($pos2 == -1){
				$flag = 0;
				
				if($num > 0){
					$row = $row + $num - 1;
					last;
				}
			}else{
				my @numList = split(/_/, $meaNumStr);
				# $meaNumStr = substr($meaNumStr, 0, $pos2);
				$meaNumStr = $numList[0];
				if($numList[1] > $num){
					$num = $numList[1];
					$flag = 1;
				}else{
					$flag = 0;
					$row = $row + $num - 1;
					last;
				}
				
			}
			
			my $pos3 = index($meaNumStr, "M");
			if ($pos3 == -1){
				$specCounterFlag = 1;
				print "\n";
				last;
			}
			
			$meaNum = $meaNumStr;
			
			print "The counter sheet name : $counterSheetName.\n";
			print "The measurement number : $meaNum.\n";
			GetMeasurementCounter($counterSheetName, $meaNum);
		}
		
		unless(@counters){
			undef @MOList;
			undef @counters;
			$row++;
			next;
		}
		
		if($specCounterFlag != 1){
			my ($objectLevel, $meaTypeElemName) = GetMeasurementObjectLevel($measurementSheet, $row, $objectLevelCol);
			my $mid = GetMeasurementMID($measurementSheet, $row, $midCol);
			GenOMesFilesByMeasurement($meaNum, $objectLevel, $meaTypeElemName, $mid);
		}
		
		undef @MOList;
		undef @counters;
		$row++;
		print "************************************************************\n";
	}
}

sub AutoGenOMesAndRelevantFilesByAD {
	
	my $ADPath = "./Input/";
	$ADPath .= "Adaptation+Description+RU30+EP1_2+OSS53+CD3+V017.xls";
	
	$parser = Spreadsheet::ParseExcel->new();  
	$workbook = $parser->Parse($ADPath);
	
	unless( -e 'Output' ){
		mkdir ('Output');
	}			

	
	GenOMesFilesByAD;
	CorrectCounterValueByRise;
	GenOMesSQLInquireCmdFile;
}

sub GetConfigValue{

	my $configFile = shift;
	
	open(OutFile,"<$configFile") || die("Can not open the file!$!n");
	my @lines = <OutFile>;
	close(OutFile);
	
	my @months;
	my @days;
	foreach my $line (@lines){
		next unless $line;
		
		if($line =~ /year/){
		
			chomp($line);
			$line =~ s/\$year=//;
			$line =~ s/\;//;
			$line =~ s/\'//g;
			$year=$line;
		}elsif($line =~ /sqlID/){
		
			chomp($line);
			$line =~ s/\$sqlID=//;
			$line =~ s/\;//;
			$line =~ s/\"//g;
			$sqlID=$line;
		}elsif($line =~ /\@month/){

			$line =~ s/\@month=//;
			$line =~ s/^\(//;
			$line =~ s/\);//;
			
			my @list = split(/,/, $line);
			foreach my $item (@list){
				next unless $item;
				if($item =~ /\./){
					my @subList = split(/\.+/, $item);
					foreach my $subItem (shift(@subList) .. pop(@subList)){
						$subItem =~ s/\s+//g;
						push(@months, $subItem);
					}
				}else{
					$item =~ s/\s+//g;
					push(@months, $item);
				}
			}
		}elsif($line =~ /\@day/){
		
			$line =~ s/\@day=//;
			$line =~ s/^\(//;
			$line =~ s/\);//;
			
			my @list = split(/,/, $line);
			foreach my $item (@list){
				next unless $item;
				if($item =~ /\./){
					my @subList = split(/\.+/, $item);
					foreach my $subItem (shift(@subList) .. pop(@subList)){
						$subItem =~ s/\s+//g;
						push(@days, $subItem);
					}
				}else{
					$item =~ s/\s+//g;
					push(@days, $item);
				}
			}
		}
	}
	
	return (\@months, \@days);
}

sub GenOMesFilesByADAndTransferToLab{

	my @refs = GetConfigValue("config.txt");
	my @months = @{$refs[0]};
	my @days = @{$refs[1]};
	
	foreach my $mValue (@months){
		
		foreach my $dValue (@days){
			
			$month = $mValue;
			$day = $dValue;
			($month, $day) = (sprintf("%02d", $month), sprintf("%02d", $day));

			print "********** Generate OMes Files of $year-$month-$day **********\n";
			
			&AutoGenOMesAndRelevantFilesByAD;
			system("TransferOMesFilesToLab.pl $year $month $day");
		}
	}
}

&GenOMesFilesByADAndTransferToLab;